<?php
################################################################################################
#  DIY Module Builder for Opencart 1.5.1.x From HostJars http://opencart.hostjars.com 		   #
################################################################################################
class ModelModuleTFilter extends Model {
	
	//Place any functions you like in here to access the DB and present data to the controller to display or otherwise
	//control the display of the view. Before writing your own functions here, check to see if you can use functions
	//in other model files, as you can just as easily pull through those models to use their functions.
	
	//Example function to get customer firstnames:
	function getFilterDetails($filterID)
	{
		$db_prefix=DB_PREFIX;
		$q="SELECT * FROM `{$db_prefix}tfm_filter` WHERE `filter_id`='{$filterID}' ORDER BY `sort_order`";
		$result=$this->db->query($q);
		$filter=$result->rows[0];
		$filter["items"]=array();
		$q="SELECT * FROM `{$db_prefix}tfm_filter_item` WHERE `filter_id`='{$filterID}' ORDER BY `sort_order`";
		$result=$this->db->query($q);
		$settings_ids=$this->makeFilterSettings($filter);
		foreach($result->rows as $item)
		{
			self::setChoices($item,$settings_ids);
			$filter["items"][]=$item;
		}
		
		return $filter;
	}
	
	private function setChoices(&$item,$settings_ids)
	{
		if(!$item["range"])
		{
			switch($item["item_type"])
			{
				case 'a': $choices=self::getChoiceAttr($item["item_id"],$settings_ids); break;
				case 'ag': $choices=self::getChoiceAttrGr($item["item_id"],$settings_ids); break;
				case 'pf': $choices=self::getChoicePf($item["product_field"],$settings_ids); break;
			}
		}
		else
		{
			$choices=self::getRangeChoices($item["product_field"],$item["range"],$settings_ids);
		}
		$item["choices"]=$choices;
	}
	
	private function getChoiceAttr($attr_id,$settings_ids)
	{
		$db_prefix=DB_PREFIX;
		$fix="LEFT JOIN `{$db_prefix}product_description` AS pd ON `pd`.`product_id`=`p`.`product_id` LEFT JOIN `{$db_prefix}product_to_store` AS `p2s` ON `p2s`.`product_id`=`p`.`product_id` WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
		AND `p`.`product_id` IN ({$settings_ids}) GROUP BY  `pa`.`text` ";
		$q=<<<SQL
		SELECT `pa`.`text` AS name , COUNT( * ) AS count 
		FROM  `{$db_prefix}product` AS p
		INNER JOIN  `{$db_prefix}product_attribute` AS pa ON  `pa`.`product_id` =  `p`.`product_id` 
		AND  `pa`.`attribute_id` ={$attr_id}
		{$fix}
SQL;
		$result=$this->db->query($q);
		return $result->rows;
	}
	
	private function getChoiceAttrGr($attribute_group_id,$settings_ids)
	{
		$db_prefix=DB_PREFIX;
		$fix="LEFT JOIN `{$db_prefix}product_description` AS pd ON `pd`.`product_id`=`p`.`product_id` LEFT JOIN `{$db_prefix}product_to_store` AS `p2s` ON `p2s`.`product_id`=`p`.`product_id` WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
		AND `p`.`product_id` IN ({$settings_ids}) GROUP BY  `pa`.`text` ORDER BY `attribute`";
		$q=<<<SQL
		SELECT `pa`.`text` AS name , COUNT( * ) AS count, `ad`.`name` AS `attribute`
		FROM  `{$db_prefix}product` AS p
		INNER JOIN  `{$db_prefix}product_attribute` AS pa ON  `pa`.`product_id` =  `p`.`product_id` 
		AND  `pa`.`attribute_id` IN (
			SELECT  `attribute_id` 
			FROM  `{$db_prefix}attribute` 
			WHERE  `attribute_group_id` ={$attribute_group_id}
		)
		INNER JOIN `{$db_prefix}attribute_description` AS ad ON `ad`.`attribute_id`=`pa`.`attribute_id`
		{$fix}
SQL;
		$result=$this->db->query($q);
		return $result->rows;
	}
	
	private function getChoicePf($field,$settings_ids)
	{
		switch($field)
		{
			case "manufacturer_id":$field_table="manufacturer";$name_column="name";$before=true;$joinon="manufacturer_id";break;
			case "tax_class_id":$field_table="tax_class";$name_column="title";$before=true;$joinon="tax_class_id";break;
			default:$before=false;
		}
		if($field=="category")
		{
			$return=array();
			$result=$this->db->query("SELECT `cd`.`name`, `c`.`category_id` FROM `".DB_PREFIX."category` AS c INNER JOIN `".DB_PREFIX."category_description` AS cd ON `cd`.`category_id`=`c`.`category_id` WHERE `c`.`parent_id`=0");
			foreach($result->rows as $row)
			{
				$children1=implode(",",$this->getCategoryChildren($row["category_id"]));
				$children1.=($children1)?"":"0";
				$children=$children1.",".$row["category_id"];
				$return[]=$this->makeCategoryChoice($row["category_id"],$row["name"],$children,$settings_ids,true);
				$newq="SELECT `cd`.`name`, `c`.`category_id` FROM `".DB_PREFIX."category` AS c INNER JOIN `".DB_PREFIX."category_description` AS cd ON `cd`.`category_id`=`c`.`category_id` WHERE `c`.`category_id` IN ({$children1})";
				foreach($this->db->query($newq)->rows as $row2)
				{
					$children=implode(",",$this->getCategoryChildren($row2["category_id"]));
					$children.=($children)?"":"0";
					$children.=",".$row["category_id"];
					$return[]=$this->makeCategoryChoice($row2["category_id"],$row2["name"],$children,$settings_ids);
				}
				
			}
			
			return $return;
		}
		if($before)
		{
			$q="SELECT `p`.`{$field}` as value, `fd`.`{$name_column}` AS `name`, COUNT(*) as count FROM `".DB_PREFIX."product` AS p INNER JOIN `".DB_PREFIX."{$field_table}` as fd ON `fd`.`{$joinon}`=`p`.`{$joinon}` LEFT JOIN `".DB_PREFIX."product_description` AS pd ON `pd`.`product_id`=`p`.`product_id` LEFT JOIN `".DB_PREFIX."product_to_store` AS `p2s` ON `p2s`.`product_id`=`p`.`product_id` WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND `p`.`product_id` IN ({$settings_ids}) GROUP BY `p`.`{$field}`";
		}
		else
		{
			if($field=="date_added")
			{
				$extract="SUBSTRING(`p`.`date_added`,1,10)";
			}
			else
			{
				$extract="`p`.`{$field}`";
			}
			$q="SELECT `p`.`{$field}` as value, {$extract} AS `name`, COUNT(*) as count FROM `".DB_PREFIX."product` AS p LEFT JOIN `".DB_PREFIX."product_description` AS pd ON `pd`.`product_id`=`p`.`product_id` LEFT JOIN `".DB_PREFIX."product_to_store` AS `p2s` ON `p2s`.`product_id`=`p`.`product_id` WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND `p`.`product_id` IN ({$settings_ids}) GROUP BY `p`.`{$field}`";
		}
		$result=$this->db->query($q);
		return $result->rows;
	}
	private function getRangeChoices($pf,$range,$settings_ids)
	{
		$q=array();
		foreach(explode(",",$range) as $range_item)
		{
			list($min,$max)=explode(":",$range_item);
			$min1=$min;
			$max1=$max;
			if($pf=="date_added")
			{
				$min.=" 00:00:00";
				$max.=" 00:00:00";
			}
			$q[]="(SELECT '{$min} - {$max}' AS value, '{$min1} - {$max1}' as name ,COUNT(*) as count FROM `".DB_PREFIX."product` AS p LEFT JOIN `".DB_PREFIX."product_description` AS pd ON `pd`.`product_id`=`p`.`product_id` LEFT JOIN `".DB_PREFIX."product_to_store` AS `p2s` ON `p2s`.`product_id`=`p`.`product_id` WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND `{$pf}`>='{$min}' AND `{$pf}`<='{$max}' AND `p`.`product_id` IN ({$settings_ids}))";
		}
		$result=$this->db->query(implode(" UNION ",$q));
		return $result->rows;
	}
	
	private function getCategoryChildren($category_id,$top=false)
	{
		if(!$top)
		{
			$top=$category_id;
		}
		$children=array();
		$result=$this->db->query("SELECT `category_id` FROM `".DB_PREFIX."category` WHERE `parent_id`='{$category_id}' AND `category_id`<>'{$top}'");
		foreach($result->rows as $row)
		{
			$children[]=$row["category_id"];
			$children=array_unique(array_merge($children,$this->getCategoryChildren($row["category_id"],$top)));
		}
		return $children;
	}
	
	private function makeCategoryChoice($category_id,$category_name,$children,$settings_ids,$master=false)
	{
		$count=$this->db->query("SELECT COUNT(DISTINCT `p`.`product_id`) AS count FROM `".DB_PREFIX."product` AS p LEFT JOIN `".DB_PREFIX."product_to_category` AS p2c ON `p2c`.`product_id`=`p`.`product_id` LEFT JOIN `".DB_PREFIX."product_description` AS pd ON `pd`.`product_id`=`p`.`product_id` LEFT JOIN `".DB_PREFIX."product_to_store` AS `p2s` ON `p2s`.`product_id`=`p`.`product_id` WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND `p2c`.`category_id` IN ({$children}) AND `p`.`product_id` IN ({$settings_ids})")->rows[0]["count"];
		return array("name"=>$category_name, "value"=>$children, "count"=>$count, "master"=>$master);
	}
	
	private function makeFilterSettings($settings)
	{
		switch($settings["range_type"])
		{
			case "a":$cond="`pa`.`attribute_id`='{$settings["range_id"]}'";break;
			case "ag":$cond="`pa`.`attribute_id` IN (SELECT `attribute_id` FROM `".DB_PREFIX."attribute` AS a WHERE `attribute_group_id`='{$settings["range_id"]}')";break;
			case "c":$cond="`p2c`.`category_id`='{$settings["range_id"]}'";break;
			case "m":$cond="`p`.`manufacturer`='{$settings["range_id"]}'";break;
			default:$cond="TRUE";
		}
		
		if($settings["instock"])
		{
			$cond="({$cond}) AND (`p`.`stock_status_id` IN ({$settings["instock"]}))";
		}
		
		$q="SELECT DISTINCT `p`.`product_id` FROM `".DB_PREFIX."product` AS p LEFT JOIN `".DB_PREFIX."product_attribute` AS pa ON `pa`.`product_id`=`p`.`product_id` LEFT JOIN `".DB_PREFIX."product_to_category` AS p2c ON `p2c`.`product_id`=`p`.`product_id` WHERE {$cond}";
		
		$ids="0";
		
		foreach($this->db->query($q)->rows as $row)
		{
			$ids.=",".$row["product_id"];
		}
		return $ids;
	}
}

?>